import os
import tkinter as tk
from tkinter import filedialog, ttk
import pandas as pd
from pathlib import Path

class ExcelSearchTool:
    """
    一个用于在多个Excel文件中搜索关键字的工具。
    可以选择多个Excel文件，搜索包含特定关键字的行，
    并按文件名分组显示结果。
    """
     
    def __init__(self, root):
        """
        初始化Excel搜索工具界面
         
        @Param {tk.Tk} root - tkinter根窗口对象
        """
        self.root = root
        self.root.title("Excel多文件搜索工具")
        self.root.geometry("1200x800")  # 增加窗口大小
         
        # 设置全局样式
        style = ttk.Style()
        style.configure("Treeview", font=('微软雅黑', 10))  # 设置树形视图字体
        style.configure("Treeview.Heading", font=('微软雅黑', 10, 'bold'))  # 设置表头字体
        style.configure("TLabelframe.Label", font=('微软雅黑', 10, 'bold'))  # 设置LabelFrame标题字体
        style.configure("TButton", font=('微软雅黑', 10))  # 设置按钮字体
         
        self.selected_files = []
        self.create_widgets()
         
    def create_widgets(self):
        """
        创建并配置界面组件
        """
        # 主容器
        main_container = ttk.Frame(self.root, padding="10")
        main_container.pack(fill="both", expand=True)
         
        # 文件选择框架
        file_frame = ttk.LabelFrame(main_container, text="选择Excel文件", padding="10")
        file_frame.pack(fill="x", pady=(0, 10))
         
        self.file_label = ttk.Label(file_frame, text="尚未选择文件", font=('微软雅黑', 10))
        self.file_label.pack(side="left", padx=(5, 10), fill="x", expand=True)
         
        select_btn = ttk.Button(file_frame, text="选择文件", command=self.select_files, width=15)
        select_btn.pack(side="right", padx=5)
         
        # 搜索框架
        search_frame = ttk.LabelFrame(main_container, text="搜索内容", padding="10")
        search_frame.pack(fill="x", pady=(0, 10))
         
        ttk.Label(search_frame, text="关键字:", font=('微软雅黑', 10)).pack(side="left", padx=(5, 10))
         
        self.search_entry = ttk.Entry(search_frame, width=50, font=('微软雅黑', 10))
        self.search_entry.pack(side="left", padx=(0, 10), fill="x", expand=True)
        
        # 绑定回车键事件
        self.search_entry.bind("<Return>", lambda event: self.search_keyword())
         
        search_btn = ttk.Button(search_frame, text="搜索", command=self.search_keyword, width=15)
        search_btn.pack(side="left")
         
        # 结果显示框架
        result_frame = ttk.LabelFrame(main_container, text="搜索结果", padding="10")
        result_frame.pack(fill="both", expand=True)
         
        # 创建带滚动条的框架
        tree_frame = ttk.Frame(result_frame)
        tree_frame.pack(fill="both", expand=True)
         
        # 创建Treeview显示结果
        self.result_tree = ttk.Treeview(tree_frame, show="headings", selectmode="browse")
        self.result_tree["columns"] = ("file", "sheet", "row", "data")
         
        # 配置列
        self.result_tree.column("file", width=200, anchor="w")
        self.result_tree.column("sheet", width=100, anchor="w")
        self.result_tree.column("row", width=80, anchor="center")
        self.result_tree.column("data", width=800, anchor="w")
         
        # 配置表头
        self.result_tree.heading("file", text="文件名", anchor="w")
        self.result_tree.heading("sheet", text="工作表", anchor="w")
        self.result_tree.heading("row", text="行号", anchor="center")
        self.result_tree.heading("data", text="数据内容", anchor="w")
         
        # 添加垂直滚动条
        scrollbar_y = ttk.Scrollbar(tree_frame, orient="vertical", command=self.result_tree.yview)
        self.result_tree.configure(yscrollcommand=scrollbar_y.set)
         
        # 添加水平滚动条
        scrollbar_x = ttk.Scrollbar(tree_frame, orient="horizontal", command=self.result_tree.xview)
        self.result_tree.configure(xscrollcommand=scrollbar_x.set)
         
        # 布局滚动条和树形视图
        self.result_tree.grid(row=0, column=0, sticky="nsew")
        scrollbar_y.grid(row=0, column=1, sticky="ns")
        scrollbar_x.grid(row=1, column=0, sticky="ew")
         
        # 配置grid权重
        tree_frame.grid_rowconfigure(0, weight=1)
        tree_frame.grid_columnconfigure(0, weight=1)
         
        # 添加进度框架
        self.progress_frame = ttk.LabelFrame(main_container, text="查询进度", padding="10")
        self.progress_frame.pack(fill="x", pady=(0, 10))
         
        self.progress_label = ttk.Label(self.progress_frame, text="", font=('微软雅黑', 10))
        self.progress_label.pack(fill="x", padx=5)
         
        self.progress_bar = ttk.Progressbar(self.progress_frame, mode='determinate')
        self.progress_bar.pack(fill="x", padx=5, pady=(5, 0))
         
        # 状态栏
        self.status_var = tk.StringVar()
        self.status_var.set("就绪")
        status_bar = ttk.Label(
            main_container, 
            textvariable=self.status_var, 
            relief="sunken", 
            anchor="w",
            font=('微软雅黑', 9),
            padding=(5, 2)
        )
        status_bar.pack(fill="x", pady=(10, 0))
         
    def select_files(self):
        """
        打开文件选择对话框，允许用户选择多个Excel文件
        """
        filetypes = [("Excel 文件", "*.xlsx;*.xls")]
        files = filedialog.askopenfilenames(filetypes=filetypes)
         
        if files:
            self.selected_files = files
            if len(files) > 1:
                self.file_label.config(text=f"已选择 {len(files)} 个文件")
            else:
                self.file_label.config(text=f"已选择: {Path(files[0]).name}")
            self.status_var.set(f"已选择 {len(files)} 个Excel文件")
         
    def search_keyword(self):
        """
        在选定的Excel文件中搜索关键字，并在Treeview中显示结果
        """
        # 清除现有的搜索结果
        for item in self.result_tree.get_children():
            self.result_tree.delete(item)
             
        keyword = self.search_entry.get().strip()
        if not keyword:
            self.status_var.set("错误: 请输入搜索关键字")
            return
             
        if not self.selected_files:
            self.status_var.set("错误: 请先选择Excel文件")
            return
             
        # 初始化进度条
        total_files = len(self.selected_files)
        self.progress_bar["maximum"] = total_files
        self.progress_bar["value"] = 0
         
        found_count = 0
        error_files = []
        empty_files = []
         
        try:
            for file_idx, file_path in enumerate(self.selected_files, 1):
                file_name = Path(file_path).name
                self.progress_label.config(text=f"正在搜索: {file_name} ({file_idx}/{total_files})")
                self.progress_bar["value"] = file_idx
                self.root.update()
                 
                try:
                    excel = pd.ExcelFile(file_path)
                    file_found = False
                     
                    for sheet_name in excel.sheet_names:
                        try:
                            df = pd.read_excel(file_path, sheet_name=sheet_name)
                            sheet_found = False
                             
                            for row_idx, row in df.iterrows():
                                row_found = False
                                row_values = []
                                 
                                for col_name, value in row.items():
                                    if isinstance(value, (str, int, float)) and str(keyword).lower() in str(value).lower():
                                        row_found = True
                                        row_values = [str(v) for v in row.values]
                                        break
                                 
                                if row_found:
                                    tag = 'even' if found_count % 2 == 0 else 'odd'
                                    values = (
                                        file_name,
                                        sheet_name,
                                        f"第{row_idx+1}行",
                                        " | ".join(row_values)
                                    )
                                    self.result_tree.insert("", "end", values=values, tags=(tag,))
                                    found_count += 1
                                    file_found = True
                                    sheet_found = True
                         
                        except Exception as sheet_error:
                            error_files.append(f"{file_name} (工作表: {sheet_name})")
                     
                    if not file_found:
                        empty_files.append(file_name)
                     
                except Exception as file_error:
                    error_files.append(file_name)
                 
                self.root.update()
         
            # 设置交替行的背景色
            self.result_tree.tag_configure('odd', background='#F5F5F5')
            self.result_tree.tag_configure('even', background='white')
             
            # 构建详细的状态信息
            status_messages = []
            if found_count > 0:
                status_messages.append(f"找到 {found_count} 个匹配项")
            if empty_files:
                status_messages.append(f"{len(empty_files)} 个文件未找到匹配内容")
            if error_files:
                status_messages.append(f"{len(error_files)} 个文件出现错误")
             
            status_text = " | ".join(status_messages)
            self.status_var.set(f"搜索完成 - {status_text}")
             
            # 更新进度标签显示详细结果
            result_details = []
            if empty_files:
                result_details.append(f"未找到匹配的文件：{', '.join(empty_files)}")
            if error_files:
                result_details.append(f"搜索出错的文件：{', '.join(error_files)}")
             
            self.progress_label.config(text="\n".join(result_details) if result_details else "搜索完成")
             
        except Exception as e:
            self.status_var.set(f"搜索过程出现错误: {str(e)}")
        finally:
            self.progress_bar["value"] = self.progress_bar["maximum"]

def main():
    """
    程序入口点
    """
    root = tk.Tk()
    app = ExcelSearchTool(root)
    root.mainloop()

if __name__ == "__main__":
    main()